﻿using System;
using System.Text;
using System.Data;
using System.Collections.Generic;

using MySql.Data.MySqlClient;
using Think.Lenovo.Com.Models;
using Think.Lenovo.Com.Common;

namespace Think.Lenovo.Com.DataAccess
{
    /// <summary>
    /// 用户每上传一次模板,记录该用户的上传情况
    /// </summary>
    public class UserDataReportInfo
    {
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public long Add(UserDataReportInfoModel model)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("insert into ")
                .Append("tbl_userdatareportinfo (templateId, userId, FilePath, uploadDate)")
                .Append("values(@templateId, @userId, @FilePath, @uploadDate)");

            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@templateId",MySqlDbType.Int32),
                    new MySqlParameter("@userId",MySqlDbType.Int32),
                    new MySqlParameter("@FilePath",MySqlDbType.VarChar,2048),
                    new MySqlParameter("@uploadDate",MySqlDbType.DateTime),
                };

            ps[0].Value = model.TemplateId;
            ps[1].Value = model.UserId;
            ps[2].Value = model.FilePath;
            ps[3].Value = model.UploadDate;

            long id = MysqlHelper.Insert(conn, sql.ToString(), ps);
            return id;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(UserDataReportInfoModel model)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Update tbl_userdatareportinfo Set ")
                .Append(" templateId=@templateId, userId=@userId, FilePath=@FilePath, uploadDate=@uploadDate ")
                .Append(" Where templateId=@templateId And userId=@userId");

            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@templateId",MySqlDbType.Int32),
                    new MySqlParameter("@userId",MySqlDbType.Int32),
                    new MySqlParameter("@FilePath",MySqlDbType.VarChar,2048),
                    new MySqlParameter("@uploadDate",MySqlDbType.DateTime),
                };

            ps[0].Value = model.TemplateId;
            ps[1].Value = model.UserId;
            ps[2].Value = model.FilePath;
            ps[3].Value = model.UploadDate;

            long id = MysqlHelper.Update(conn, sql.ToString(), ps);
            return (int)id;
        }

        /// <summary>
        /// 根据模板id 和 用户id 得到一次上传的情况
        /// </summary>
        /// <param name="templateId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public UserDataReportInfoModel Get(int templateId, int userId)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            UserDataReportInfoModel model = new UserDataReportInfoModel();
            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Select * From  tbl_userdatareportinfo ")
                .Append(" Where templateId=" + templateId + " And userId=" + userId);
            DataSet ds = MysqlHelper.Query(conn, sql.ToString(), null);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                        model.TemplateId = Convert.ToInt32(ds.Tables[0].Rows[0]["templateId"]);
                        model.UserId = Convert.ToInt32(ds.Tables[0].Rows[0]["userId"]);
                        model.FilePath = Convert.ToString(ds.Tables[0].Rows[0]["FilePath"]);
                        model.UploadDate = DateTime.Now;
                    }
                }
            }
            return model;
        }

        public UserDataReportInfoModel Get(int id)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            UserDataReportInfoModel model = new UserDataReportInfoModel();
            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Select * From  tbl_userdatareportinfo ")
                .Append(" Where " +" Id=" + id);
            DataSet ds = MysqlHelper.Query(conn, sql.ToString(), null);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                        model.TemplateId = Convert.ToInt32(ds.Tables[0].Rows[0]["templateId"]);
                        model.UserId = Convert.ToInt32(ds.Tables[0].Rows[0]["userId"]);
                        model.FilePath = Convert.ToString(ds.Tables[0].Rows[0]["FilePath"]);
                        model.UploadDate = DateTime.Now;
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 根据模板id 得到一个LinkedList<UserDataReortInfoModel>的集合
        /// </summary>
        /// <param name="templateId">模板id</param>
        /// <returns></returns>
        public LinkedList<UserDataReportInfoModel> GetListByTemplateId(int templateId, int pageIndex, int pageSize)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            LinkedList<UserDataReportInfoModel> list = new LinkedList<UserDataReportInfoModel>();
            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Select A.*,b.realName,C.reportName From  tbl_userdatareportinfo As A left join tbl_userInfo As B on a.UserId=b.userId left join tbl_datareporttemplate As c on A.templateId=C.id ")
                .Append(" Where A.templateId=" + templateId + " Order by A.Id desc ")
                .Append(" Limit " + (pageIndex - 1) * pageSize + "," + pageSize);
            DataSet ds = MysqlHelper.Query(conn, sql.ToString(), null);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        UserDataReportInfoModel model = new UserDataReportInfoModel();
                        model.Id = Convert.ToInt32(row["Id"]);
                        model.TemplateId = Convert.ToInt32(row["templateId"]);
                        model.TemplateName = Convert.ToString(row["reportName"]);
                        model.UserId = Convert.ToInt32(row["userId"]);
                        model.UploadeUserName = Convert.ToString(row["realName"]);
                        model.FilePath = Convert.ToString(row["FilePath"]);
                        model.UploadDate = DateTime.Now;
                        list.AddLast(model);
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 根据用户id 得到一个LinkedList<UserDataReortInfoModel>的集合
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <returns></returns>
        public LinkedList<UserDataReportInfoModel> GetListByUserId(int userId)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            LinkedList<UserDataReportInfoModel> list = new LinkedList<UserDataReportInfoModel>();
            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Select * From  tbl_userdatareportinfo ")
                .Append(" Where templateId=" + userId);
            DataSet ds = MysqlHelper.Query(conn, sql.ToString(), null);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        UserDataReportInfoModel model = new UserDataReportInfoModel();
                        model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                        model.TemplateId = Convert.ToInt32(ds.Tables[0].Rows[0]["templateId"]);
                        model.UserId = Convert.ToInt32(ds.Tables[0].Rows[0]["userId"]);
                        model.FilePath = Convert.ToString(ds.Tables[0].Rows[0]["FilePath"]);
                        model.UploadDate = DateTime.Now;
                        list.AddLast(model);
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 根据模板id得到该模板收集了多少份报表文件
        /// </summary>
        /// <param name="templateId">模板id</param>
        /// <returns></returns>
        public int GetCountByTemplateId(int templateId)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            LinkedList<UserDataReportInfoModel> list = new LinkedList<UserDataReportInfoModel>();
            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Select Count(Id) From  tbl_userdatareportinfo ")
                .Append(" Where templateId=" + templateId);

            Object obj = MysqlHelper.ExecuteScalar(conn, sql.ToString(), null);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 根据用户id 得到该用户上传了多少个报表文件 
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <returns></returns>
        public int GetCountByUserId(int userId)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            LinkedList<UserDataReportInfoModel> list = new LinkedList<UserDataReportInfoModel>();
            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Select Count(Id) From  tbl_userdatareportinfo ")
                .Append(" Where userId=" + userId);

            Object obj = MysqlHelper.ExecuteScalar(conn, sql.ToString(), null);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }
    }
}
